package clothing.database;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import java.sql.Connection;

public class CustomerManager {

	




			dbase db;

			public CustomerManager() {
				db = new dbase();
			}

			public void insert(String firstname, String lastname, String street, String city, 
					String state, int zip, String email) {
				try {
					Class.forName("com.mysql.jdbc.Driver").newInstance();
					Connection conn = DriverManager.getConnection(
							"jdbc:mysql://199.17.162.76/Clothing", "root", "");

					System.out.println("Connected");
					//Statement st = conn.createStatement();
					
//					
//					String query = "insert into employees values('','" + firstname
//							+ "','" + mi + "','" + lastname + "','" + phone + "','"
//							+ address + "','" + type + "','" + username + "','"
//							+ password + "')";
//					System.out.println(query);
					
					PreparedStatement p = conn.prepareStatement("insert into customers values('',?,?,?,?,?,?,?)");
					p.setString(1, firstname);
					p.setString(2, lastname);
					p.setString(3, street);
					p.setString(4, city);
					p.setString(5, state);
					p.setInt(6, zip);
					p.setString(7, email);
					
					
					
					p.executeUpdate();
					
				} catch (InstantiationException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (ClassNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

			}

			
			public ArrayList<Customer> getList() {
				ArrayList<Customer> list = new ArrayList();

				this.db.open();
				try {
					Statement s = this.db.getConn().createStatement();
					String query = "SELECT * from customers";

					ResultSet rs = s.executeQuery(query);

					while (rs.next()) {

						int customer_id = rs.getInt("Customer ID");
						String firstName = rs.getString("First Name");
						String lastName = rs.getString("Last Name");
						String street = rs.getString("Street");
						String city = rs.getString("City");
						String state = rs.getString("State");
						int zip = rs.getInt("Zip");
						String email = rs.getString("Email");
						
						

						Customer c = new Customer(customer_id, firstName, lastName, 
								street, city, state, zip, email);
						list.add(c);
					}

				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				return list;
			}
			
		
			
			public void remove(String id)
			{
				this.db.open();
				Statement s;
				try {
					s = this.db.conn.createStatement();

					String query ="Delete from customers where `Customer ID` = '"+id+"'";
					
					s.execute(query);
					
					System.out.println(id + "Deleted Successfully");
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
			
			public Customer getCustomer(int id)
			{
				int iid = 0;
				String firstname = "";
				String lastname = ""; 
				String street = "";
				String city = "";
				String state = "";
				int zip = 0;
				String email = "";
			
				this.db.open();
				Statement s;
				try {
					s = this.db.conn.createStatement();

					String query ="Select * from customers where `Customer ID` = '"+id+"'";
					
					
					ResultSet rs = s.executeQuery(query);
					while (rs.next()) 
					{
				    iid = rs.getInt("Customer ID");
					firstname = rs.getString("First Name");
					lastname = rs.getString("Last Name");
					street = rs.getString("Street");
					city = rs.getString("City");
					state = rs.getString("State");
					zip = rs.getInt("Zip");
					email = rs.getString("Email");
					}				

				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				System.out.println("cm"+firstname);
				Customer c = new Customer(iid, firstname, lastname, 
						street, city, state, zip, email);
				return c;
			}
			
			public void updateCustomer(int iid, String firstname, String lastname, 
					String street, String city, String state, int zip, String email)
		    {
		    	this.db.open();
				try {
					Statement s = this.db.getConn().createStatement();
					String query = "UPDATE customers SET `First Name`= '"+firstname+"',  " +
							"`Last Name` = '"+lastname+"', Street = '"+street+"', City = '"+city+"', " +
							"State= '"+state+"', Zip= '" + zip+"', Email='"+ email+"'" +
									"Where `Customer ID` ='"+iid+"'";
							
					s.execute(query);
		    }catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		    }
		

	

}
